module.exports = {
    SubmitStatistics: `with cte as(
        SELECT
          B.ProcessGroupName ProcessGroupName,
          B.ProcessGroupID ProcessGroupID,
          B.Title Title,
          B.SerialNumber SerialNumber,
          B.ProjectName ProjectName,
          B.ProcStartDate ProcStartDate,
          B.Amount Amount,
          B.Purpose Purpose,
          B.LastApprovedDate LastApproveDate,
          B.ProcessID ProcessID,
          B.ID BizFormID,
          B.ProcessStatus ProcessStatus,
          '' EntryState,
          B.ProcessName 
        FROM K2_BizCommObj B WITH (NOLOCK) 
        WHERE b.ProcessStatus in (0,1,2,3,4)
      ),
      total as(
        select 
          DATEDIFF(DAY, ProcStartDate, GETDATE()) IntervalDay,
          COUNT(*) Numbers
        from cte
        GROUP BY DATEDIFF(DAY, ProcStartDate, GETDATE())
      )
      select '今日提交' Name, SUM(Numbers) Value from total where IntervalDay=0
      union all
      select '昨日提交', SUM(Numbers) from total where IntervalDay=1
      union all
      select '更早以前', SUM(Numbers) from total where IntervalDay>=2`,
    AuditStatistics: `with cte as(
        SELECT
          A.ID,
          B.ProcessGroupName ProcessGroupName,
          B.ProcessGroupID ProcessGroupID,
          B.Title Title,
          B.SerialNumber SerialNumber,
          B.ProjectName ProjectName,
          B.ProcStartDate ProcStartDate,
          B.Amount Amount,
          B.Purpose Purpose,
          B.LastApprovedDate LastApproveDate,
          B.ProcessID ProcessID,
          B.ProcessName ProcessName,
          B.ID BizFormID,
          A.URL
        FROM K2_FinishedList A
        INNER JOIN K2_BizCommObj B WITH (nolock) ON A.ProcessID= B.ProcessID AND A.BizFormID= B.ID
      ),
      total as(
        SELECT 
          DATEDIFF(DAY, LastApproveDate, GETDATE()) IntervalDay,
          COUNT(*) Numbers
        from cte
        GROUP BY DATEDIFF(DAY, LastApproveDate, GETDATE())
      )
      select '今日审批' Name, SUM(Numbers) Value from total where IntervalDay=0
      union ALL
      select '昨日审批', SUM(Numbers) from total where IntervalDay=1
      union all
      select '更早以前', SUM(Numbers) from total where IntervalDay>=2`,
    SubmitDetailsStatistics: `
      with cte as(
        SELECT
          B.ProcessGroupName ProcessGroupName,
          B.ProcessGroupID ProcessGroupID,
          B.Title Title,
          B.SerialNumber SerialNumber,
          B.ProjectName ProjectName,
          B.ProcStartDate ProcStartDate,
          B.Amount Amount,
          B.Purpose Purpose,
          B.LastApprovedDate LastApproveDate,
          B.ProcessID ProcessID,
          B.ID BizFormID,
          B.ProcessStatus ProcessStatus,
          '' EntryState,
          B.ProcessName 
        FROM K2_BizCommObj B WITH (NOLOCK) 
        WHERE b.ProcStartDate is not null and b.ProcessStatus in (0,1,2,3,4) #wheres
      )
      select
        #groups
        convert(varchar(7), ProcStartDate, 120) as YearMonth,
        count(*) Counts
      from cte
      GROUP BY #groups convert(varchar(7), ProcStartDate, 120)
      order by convert(varchar(7), ProcStartDate, 120) desc`,
    AuditDetailsStatistics:`
      with cte as(
        SELECT
          A.ID,
          B.ProcessGroupName ProcessGroupName,
          B.ProcessGroupID ProcessGroupID,
          B.Title Title,
          B.SerialNumber SerialNumber,
          B.ProjectName ProjectName,
          B.ProcStartDate ProcStartDate,
          B.Amount Amount,
          B.Purpose Purpose,
          B.LastApprovedDate LastApproveDate,
          B.ProcessID ProcessID,
          B.ProcessName ProcessName,
          B.ID BizFormID,
          A.URL
        FROM K2_FinishedList A
        INNER JOIN K2_BizCommObj B WITH (nolock) ON A.ProcessID=B.ProcessID AND A.BizFormID=B.ID
        WHERE 1=1 #wheres
      )
      select
        #groups
        convert(varchar(7), LastApproveDate, 120) as YearMonth,
        count(*) Counts
      from cte
      GROUP BY #groups convert(varchar(7), LastApproveDate, 120)
      order by convert(varchar(7), LastApproveDate, 120) desc`,
    GetBizCommonInfo: `select * from K2_BizCommObj a`,
    GetWorklist: 'select * from K2_Worklist a',
    GetDestinationUsers: `select a.* from K2_DestinationUsers a
      inner join K2_BizCommObj b on b.ID=a.FormInstanceID and b.ProcessID=a.ProcessListID`,
    GetAuditingLogs: `select a.* from K2_AuditingLog a
    inner join K2_BizCommObj b on b.ID=a.BizFormID and b.ProcessID=a.ProcessID`,
    GetPageAccesses: `
      with cte as(
        select
          a.ID as BizFormId,
          a.ProcStartDate,
          a.SerialNumber,
          a.Title,
          a.ProcessName,
          a.ProjectID,
          a.ProjectName,
          a.DepartmentName,
          a.BizFormUrl
        from K2_BizCommObj a
        where a.SerialNumber in (@serialNumbers)
      ), 
      cte2 as(
        select * from cte
        outer apply(
          select
            p.AccessType,
            p.LoadedDateTime,
            p.CompletedDateTime,
            p.RecordMark,
            p.ReceiveMessage
          from PageAccessRecord p 
          where p.PageUrl like cte.BizFormUrl+'%'
        ) p
      )
      select
        BizFormId,
        min(SerialNumber) SerialNumber,
        min(Title) Title,
        min(ProcessName) ProcessName,
        min(ProjectID) ProjectID,
        min(ProjectName) ProjectName,
        min(DepartmentName) DepartmentName,
        min(AccessType) AccessType,
        LoadedDateTime,
        MAX(CompletedDateTime) CompletedDateTime,
        max(RecordMark) as RecordMark
      from cte2
      group by BizFormId, LoadedDateTime
      order by LoadedDateTime desc`,
    PageAccessStatistics: `select 
      min(a.LoadedDateTime) AS LoadedDateTime,
      a.OriginalPageUrl,
      a.PageUrl,
      max(a.CompletedDateTime) as CompletedDateTime,
      DATEPART(SECOND, MAX(a.CompletedDateTime)-min(a.LoadedDateTime)) as IntervalSeconds,
      p.UserId
    from PageAccessRecord a
    outer apply(
      select top 1 * from K2_SystemUser where UserAccount=a.AccessUserAccount
    ) p
    group by a.PageUrl, a.LoadedDateTime, p.UserId, a.OriginalPageUrl
    order by a.LoadedDateTime desc`,

    GetThirdPartyLogs: `select * from API_CallThirdPartyLog a where a.BizFormID=@bizFormId`,

    GetApiLog: `select 
        top 300 * 
      from API_Log a
      where a.Controller=@controller and Action=@action and
        a.AppID=@appId and a.RequestContent like '%'+@content+'%'
      order by a.CreatedOn desc;`,

    RedoThirdPartyLog: `update API_CallThirdPartyLog set SendTimes=0 where id=@id`,

    GetDoaItemDetail: `select * from K2_DOAItem a where a.DOAItemID=@doaItemId;`,
    
    GetDOAItems: `with cte as(
      select
        a.*,
        (
          select 
            cast(r.ID as varchar(20)) + ' - ' + case when ISNULL(r.DisplayName, '')='' then r.Title else r.DisplayName end as d
          from K2_RoleType r 
          where CHARINDEX('|' + cast(r.ID as varchar(20)) + '|', '|' + REPLACE(REPLACE(ISNULL(a.RoleExpression, ''),' ', ''), '&', '|') + '|')<>0
          for xml path('')
        ) as x
      from K2_DOAColumns_Items a
    )
    select
      cte.DOAColumnName,
      cte.NodeSeq,
      cte.Action,
      cte.RoleExpression,
      cte.K2ActivityName,
      cte.PreExcuteExpression,
      cte.PreNotifyExpression,
      cte.IsMustParticipant,
      cte.CanUploadAttachment,
      cte.CurrentNodeCallBackApi,
      cte.IsToMail,
      cte.IsThirdCanEdit,
      -- cte.IsCanOperControl,
      cte.IsThirdEmailNode,
      REPLACE(REPLACE(cte.x, '<d>', ''), '</d>', ',') as Roles
    from cte
    where DOAItemID=@doaItemId;`,

    AddMonitor: `insert into #dbName.#tableName 
      (
        RecordDateTime,
        Connections,
        Server,
        Accepts,
        Requests,
        Reading,
        Writing,
        Waiting
      )
      values
      (
        current_timestamp(),
        @connections,
        @server,
        @accepts,
        @requests,
        @reading,
        @writing,
        @waiting
      )`,
    GetMonitorDatas: `
      select 
        max(RecordDateTime) AS RecordDateTime,
        round(#aggregate(Connections), 2) as Connections,
        round(#aggregate(Server), 2) AS Server,
        round(#aggregate(Accepts), 2) AS Accepts,
        round(#aggregate(Requests), 2) AS Requests,
        round(#aggregate(Reading), 2) AS Reading,
        round(#aggregate(Writing), 2) AS Writing,
        round(#aggregate(Waiting), 2) AS Waiting
      from #dbName.#tableName 
      where date_format(RecordDateTime, '%Y-%m-%d')<@currentDate 
      group by floor(date_format(RecordDateTime, '%Y%m%d%H') div 3)
      union
      select 
        max(RecordDateTime) AS RecordDateTime,
        round(#aggregate(Connections), 2) as Connections,
        round(#aggregate(Server), 2) AS Server,
        round(#aggregate(Accepts), 2) AS Accepts,
        round(#aggregate(Requests), 2) AS Requests,
        round(#aggregate(Reading), 2) AS Reading,
        round(#aggregate(Writing), 2) AS Writing,
        round(#aggregate(Waiting), 2) AS Waiting
      from #dbName.#tableName 
      where date_format(RecordDateTime, '%Y-%m-%d')=@currentDate 
      group by floor(date_format(RecordDateTime, '%Y%m%d%H%i%s') div @interval)
      limit 5000`,
    
    ClearStatusDatas: `CALL #dbName.sp_clear_status()`
};